﻿// Copyright Microsoft

using System;
using System.Collections.Generic;
using System.Activities;
using System.Data.SqlClient;
using System.ComponentModel;
using System.Data;
using System.Collections.ObjectModel;
using System.Xml.Linq;
using Microsoft.VisualBasic.Activities;
using System.Linq;
using System.Text;
using Microsoft.Samples.SqlServer.Activities.ActivityPublishers;

namespace Microsoft.Samples.SqlServer.Activities
{

  public abstract class SqlServerBaseActivity : NativeActivity
  {
    [RequiredArgument]
    public InArgument<SqlConnectionStringBuilder> SqlConnectionBuilder { get; set; }
  }

  [Designer(typeof(Microsoft.Samples.SqlServer.Activities.Designers.SelectDataTableDesigner))]
  public sealed class SelectDataTableActivity : SqlServerBaseActivity
  {
    [Browsable(false)]
    public Collection<Activity> WhereActivities { get; set; }

    [Browsable(false)]
    public Activity DocumentPartActivity { get; set; }
    [Browsable(false)]
    public OutArgument<DocumentPart> DocumentPart { get; set; }

    [RequiredArgument]
    public string Schema { get; set; }
    [RequiredArgument]
    public string TableSource { get; set; }
    [RequiredArgument]
    public string ColumnNames { get; set; }

    public OutArgument<DataTable> DataTable { get; set; }

    //Drop Activity
    public SelectDataTableActivity()
    {
      WhereActivities = new Collection<Activity>();
    }

    //Drop Activity
    //Cache composite activity metadata
    protected override void CacheMetadata(NativeActivityMetadata metadata)
    {
      metadata.SetChildrenCollection(WhereActivities);

      base.CacheMetadata(metadata);
    }

    protected override void Execute(NativeActivityContext context)
    {
      using (SqlConnection connection = new SqlConnection(SqlConnectionBuilder.Get(context).ToString()))
      {
        using (SqlCommand command = connection.CreateCommand())
        {
          connection.Open();

          this.BuildSelectCommand(command, this.ColumnNames, this.TableSource);
        
          DataTable dt = new DataTable();

          if (command.CommandText != string.Empty)
          {
              SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
              dataAdapter.Fill(dt);
          }

          DataTable.Set(context, dt);
        }
      }

      DocumentPart.Set(context, this.DocumentPartActivity);
    }

    private SqlCommand BuildSelectCommand(SqlCommand command, string columnNames, string viewName)
    {
        string where = string.Empty;
        if (this.WhereActivities.Count == 0)
        {
            command.CommandText = String.Format("SELECT {0} FROM {1}", columnNames, viewName);
        }
        else
        {
            StringBuilder whereStringBuilder = new StringBuilder();
            SqlWhere activity = null;        

            for (int i = 0; i < this.WhereActivities.Count; i++)
            {
                activity = this.WhereActivities[i] as SqlWhere;
                if (activity.Value != null)
                {
                    string parameterName = activity.ColumnName.ToString()
                        .Replace("[", string.Empty).Replace("]", string.Empty).Replace(" ", string.Empty);

                    string columnName = activity.ColumnName.ToString();

                    if (i < this.WhereActivities.Count - 1)
                        whereStringBuilder.Append(String.Format(" {0} {1} @{2} {3}", columnName,
                            ConvertComparisonOperator(activity.SqlComparisonOperator), parameterName,
                            activity.LogicalOperator.ToString().ToUpper()));
                    else
                        whereStringBuilder.Append(String.Format(" {0} {1} @{2}", columnName,
                            ConvertComparisonOperator(activity.SqlComparisonOperator), parameterName));

                    if (activity.ValueType == TypeEnum.String)
                    {
                        string value = activity.Value.ToString().Replace("\"", string.Empty);
                        command.Parameters.Add(String.Format("@{0}", parameterName), SqlDbType.VarChar,
                            value.ToString().Length).Value = value;
                    }
                    else
                        command.Parameters.Add(String.Format("@{0}", parameterName), ConvertType(activity.ValueType)).Value = activity.Value;
                }
            }
            if (this.WhereActivities.Count == command.Parameters.Count)
            {
                command.CommandText = String.Format("SELECT {0} FROM {1} WHERE {2}", columnNames, viewName, whereStringBuilder.ToString());
            }
        }
        return command;
    }

    //NEXT: IsNull, IsNotNull
    private string ConvertComparisonOperator(SqlComparisonOperatorEnum comparisonOperatorEnum)
    {
      string comparisonOperator = string.Empty;

      switch (comparisonOperatorEnum)
      {
          case SqlComparisonOperatorEnum.Eq:
          comparisonOperator = "=";
          break;
          case SqlComparisonOperatorEnum.Neq:
          comparisonOperator = "<>";
          break;
          case SqlComparisonOperatorEnum.Gt:
          comparisonOperator = ">";
          break;
          case SqlComparisonOperatorEnum.Geq:
          comparisonOperator = ">=";
          break;
          case SqlComparisonOperatorEnum.Lt:
          comparisonOperator = "<";
          break;
          case SqlComparisonOperatorEnum.Leq:
          comparisonOperator = "<=";
          break;
          case SqlComparisonOperatorEnum.Like:
          comparisonOperator = "LIKE";
          break;
      }

      return comparisonOperator;
    }

    private SqlDbType ConvertType(TypeEnum typeEnum)
    {
      SqlDbType dbType = SqlDbType.VarChar;
 
      switch (typeEnum)
      {
        case TypeEnum.String:
          dbType = SqlDbType.VarChar;
          break;
        case TypeEnum.Number:
          dbType = SqlDbType.Int;
          break;
        case TypeEnum.Currency:
          dbType = SqlDbType.Money;
          break;
        case TypeEnum.DateTime:
          dbType = SqlDbType.DateTime;
          break;
      }

      return dbType;
    }

    private string SafeCommandText(string commandText)
    {
      commandText = commandText.Replace(";", string.Empty);
      commandText = commandText.Replace("--", string.Empty);
      return commandText;
    }
  }
}

      